<!DOCTYPE html>
<html lang="zh-cn">
  <head>
    <meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="generator" content="Hugo 0.82.0" />


<title>数据库与 R 包 dbplyr 交互 - 宇飞的世界</title>
<meta property="og:title" content="数据库与 R 包 dbplyr 交互 - 宇飞的世界">


  <link href='/favicon.ico' rel='icon' type='image/x-icon'/>



  








<link href='//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/github.min.css' rel='stylesheet' type='text/css' />



<link rel="stylesheet" href="/css/fonts.css" media="all">
<link rel="stylesheet" href="/css/main.css" media="all">



  </head>
  <body>
    <div class="wrapper">
      <header class="header">
        <nav class="nav">
  <a href="/" class="nav-logo">
    <img src="/images/logo.png"
         width="50"
         height="50"
         alt="Logo">
  </a>

  <ul class="nav-links">
    
    <li><a href="/about/">About</a></li>
    
    <li><a href="/categories/">Categories</a></li>
    
    <li><a href="/tags/">Tags</a></li>
    
    <li><a href="http://www.zhongyufei.com/Rbook/_book">R study</a></li>
    
  </ul>
</nav>

      </header>


<main class="content" role="main">

  <article class="article">
    
    <span class="article-duration">17 min read</span>
    

    <h1 class="article-title">数据库与 R 包 dbplyr 交互</h1>

    
    <span class="article-date">2021-06-11</span>
    

    <div class="article-content">
      
<script src="/2021/06/11/dbplyr-study/index_files/header-attrs/header-attrs.js"></script>
<style type="text/css">
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { display: inline-block; line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
  { counter-reset: source-line 0; }
pre.numberSource code > span
  { position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
  { content: counter(source-line);
    position: relative; left: -1em; text-align: right; vertical-align: baseline;
    border: none; display: inline-block;
    -webkit-touch-callout: none; -webkit-user-select: none;
    -khtml-user-select: none; -moz-user-select: none;
    -ms-user-select: none; user-select: none;
    padding: 0 4px; width: 4em;
    color: #aaaaaa;
  }
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa;  padding-left: 4px; }
div.sourceCode
  {  background-color: #f8f8f8; }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ef2929; } /* Alert */
code span.an { color: #8f5902; font-weight: bold; font-style: italic; } /* Annotation */
code span.at { color: #c4a000; } /* Attribute */
code span.bn { color: #0000cf; } /* BaseN */
code span.cf { color: #204a87; font-weight: bold; } /* ControlFlow */
code span.ch { color: #4e9a06; } /* Char */
code span.cn { color: #000000; } /* Constant */
code span.co { color: #8f5902; font-style: italic; } /* Comment */
code span.cv { color: #8f5902; font-weight: bold; font-style: italic; } /* CommentVar */
code span.do { color: #8f5902; font-weight: bold; font-style: italic; } /* Documentation */
code span.dt { color: #204a87; } /* DataType */
code span.dv { color: #0000cf; } /* DecVal */
code span.er { color: #a40000; font-weight: bold; } /* Error */
code span.ex { } /* Extension */
code span.fl { color: #0000cf; } /* Float */
code span.fu { color: #000000; } /* Function */
code span.im { } /* Import */
code span.in { color: #8f5902; font-weight: bold; font-style: italic; } /* Information */
code span.kw { color: #204a87; font-weight: bold; } /* Keyword */
code span.op { color: #ce5c00; font-weight: bold; } /* Operator */
code span.ot { color: #8f5902; } /* Other */
code span.pp { color: #8f5902; font-style: italic; } /* Preprocessor */
code span.sc { color: #000000; } /* SpecialChar */
code span.ss { color: #4e9a06; } /* SpecialString */
code span.st { color: #4e9a06; } /* String */
code span.va { color: #000000; } /* Variable */
code span.vs { color: #4e9a06; } /* VerbatimString */
code span.wa { color: #8f5902; font-weight: bold; font-style: italic; } /* Warning */
</style>

<div id="TOC">
<ul>
<li><a href="#前言">前言</a></li>
<li><a href="#dbplyr-介绍">dbplyr 介绍</a></li>
<li><a href="#连接数据库">连接数据库</a></li>
<li><a href="#查询数据库">查询数据库</a></li>
<li><a href="#函数解释">函数解释</a>
<ul>
<li><a href="#内存数据库">内存数据库</a></li>
<li><a href="#tbl">tbl</a></li>
<li><a href="#copy_to">copy_to</a></li>
<li><a href="#compute">compute</a></li>
<li><a href="#memdb_frame-系列">memdb_frame 系列</a></li>
<li><a href="#simulate_dbi-系列">simulate_dbi 系列</a></li>
<li><a href="#dbplyr-支持的动词">dbplyr 支持的动词</a></li>
</ul></li>
<li><a href="#案例">案例</a>
<ul>
<li><a href="#ms-sql-server">MS SQL Server</a></li>
<li><a href="#oracle">Oracle</a></li>
<li><a href="#其他数据库">其他数据库</a></li>
</ul></li>
<li><a href="#dbplyr-总结">dbplyr 总结</a></li>
<li><a href="#参考资料">参考资料</a></li>
</ul>
</div>

<div id="前言" class="section level2">
<h2>前言</h2>
<p>dbplyr 将 dplyr 包的函数转化为 SQL 语句去服务器(数据库)获取数据,在数据量较大、计算较多时,将远程连接数据库中
的表当作内存中的数据使用.当你使用的本机内存不够大时,可以充分利用服务器的性能(内存),这样做的好处不言而喻.</p>
<p>至于为什么使用 dbplyr 而不是直接编写 SQL,本人觉得优势如下所示：</p>
<ul>
<li><p>dbplyr 写起来简洁高效,基本跟用 dplyr 没有差别</p></li>
<li><p>能利用数据库所在服务器的算力,配合上并行计算,在处理大量数据时,大大加快速度.</p></li>
<li><p>不同数据库的语法存在差异,当源数据存在不同数据库时,用 R 包 dbplyr 和 dplyr 的统一语法,清洗数据加快效率</p></li>
<li><p>通过 dplyr 动词方便实现复杂的逻辑,当逻辑越复杂时 dbplyr 的优势越明显,不用一层层嵌套语句.</p></li>
</ul>
<p>当然也有一些劣势,比如在SQL Server 中 SQL 可以跨库查询,但是 dbplyr 无法直接跨库查询.</p>
<blockquote>
<p>dbplyr 在 oracle 可以跨不同 schema 查询</p>
</blockquote>
<p><a href="https://dbplyr.tidyverse.org/articles/dbplyr.html">dbplyr项目地址</a></p>
</div>
<div id="dbplyr-介绍" class="section level2">
<h2>dbplyr 介绍</h2>
<p>我们想要在数据库中使用<a href="https://bookdown.org/zhongyufei/Data-Handling-in-R/Data-Manipulation-dplyr.html">dplyr</a> ,需要先安装 dbplyr：</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a><span class="fu">install.packages</span>(<span class="st">&quot;dbplyr&quot;</span>)</span></code></pre></div>
<p>还需要有 R 包 DBI, DBI提供通用的接口,允许 dplyr 使用相同的代码函数处理不同的数据框,DBI 是 dbplyr 的依赖包,会跟随 dbplyr 安装.在我们使用dbplyr之前,需要确保已有数据库后端,比如常用的 <code>RMariaDB</code>,<code>RPostgres</code>,<code>RSQLite</code>,<code>odbc</code>,<code>ROracle</code>.如我常用 dbplyr 操作 MS SQL Server,就是使用 <code>odbc</code> 后端.</p>
<p>在本文介绍中,我们将主要使用<code>RSQLite</code>后端,因为这个数据库完全嵌入在R包,不依赖本地数据库环境,也不需要设置数据库服务器.所以说SQLite非常适合演示 『dbplyr 与 数据库交互』.本文将通过演示与 SQLite 交互,学习使用 dbplyr 包;最后在实际案例中演示,dbplyr 通过后端<code>odbc</code> 与 MS SQL Server 交互.</p>
<p>另外关于 R 语言 与数据库的交互,请参考我的<a href="https://bookdown.org/zhongyufei/Data-Handling-in-R/database.html">数据处理笔记</a>数据库章节.</p>
</div>
<div id="连接数据库" class="section level2">
<h2>连接数据库</h2>
<p>在 dbplyr 中使用数据库,首先需要通过<code>DBI::dbConnect()</code>连接数据库,关于怎么连接数据库详见数据处理笔记.</p>
<p>使用RSQLite 创建内存数据库:</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(dplyr)</span></code></pre></div>
<pre><code>## 
## 载入程辑包：&#39;dplyr&#39;</code></pre>
<pre><code>## The following objects are masked from &#39;package:stats&#39;:
## 
##     filter, lag</code></pre>
<pre><code>## The following objects are masked from &#39;package:base&#39;:
## 
##     intersect, setdiff, setequal, union</code></pre>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(DBI)</span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(RSQLite)</span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(dbplyr)</span></code></pre></div>
<pre><code>## 
## 载入程辑包：&#39;dbplyr&#39;</code></pre>
<pre><code>## The following objects are masked from &#39;package:dplyr&#39;:
## 
##     ident, sql</code></pre>
<div class="sourceCode" id="cb9"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(<span class="fu">SQLite</span>(), <span class="at">dbname =</span> <span class="st">&quot;:memory:&quot;</span>)</span></code></pre></div>
<p>一般情况下,数据库存在另外一台服务器上,我们需要这样连接,以 MS SQL 举例:</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" aria-hidden="true" tabindex="-1"></a>con_study <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(odbc<span class="sc">::</span><span class="fu">odbc</span>(),</span>
<span id="cb10-2"><a href="#cb10-2" aria-hidden="true" tabindex="-1"></a>  <span class="at">.connection_string =</span></span>
<span id="cb10-3"><a href="#cb10-3" aria-hidden="true" tabindex="-1"></a>    <span class="st">&quot;driver={ODBC Driver 17 for SQL Server};</span></span>
<span id="cb10-4"><a href="#cb10-4" aria-hidden="true" tabindex="-1"></a><span class="st">                    server=192.168.2.236;</span></span>
<span id="cb10-5"><a href="#cb10-5" aria-hidden="true" tabindex="-1"></a><span class="st">                    database=study;</span></span>
<span id="cb10-6"><a href="#cb10-6" aria-hidden="true" tabindex="-1"></a><span class="st">                    uid=zhongyf;</span></span>
<span id="cb10-7"><a href="#cb10-7" aria-hidden="true" tabindex="-1"></a><span class="st">                    pwd=Zyf123456&quot;</span>, <span class="at">timeout =</span> <span class="dv">10</span></span>
<span id="cb10-8"><a href="#cb10-8" aria-hidden="true" tabindex="-1"></a>)</span></code></pre></div>
<p>我们的临时内存数据库中暂时没有数据,需要我们将数据暂时保存到数据库中.通过<code>dplyr::copy_to()</code>即可:</p>
<div class="sourceCode" id="cb11"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" aria-hidden="true" tabindex="-1"></a><span class="fu">copy_to</span>(con, nycflights13<span class="sc">::</span>flights, <span class="at">name =</span> <span class="st">&quot;flights&quot;</span>)</span></code></pre></div>
<p>现在在我们的临时数据库中已经有数据集了,我们可以通过<code>dbplyr::tbl()</code>来查看引用表:</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a>flights_db <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con, <span class="st">&quot;flights&quot;</span>)</span></code></pre></div>
<p>当我们查看获取的数据时,我们发现和本地的数据源导入R后基本一样:</p>
<div class="sourceCode" id="cb13"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a>flights_db</span></code></pre></div>
<pre><code>## # Source:   table&lt;flights&gt; [?? x 19]
## # Database: sqlite 3.35.5 [:memory:]
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    &lt;int&gt; &lt;int&gt; &lt;int&gt;    &lt;int&gt;          &lt;int&gt;     &lt;dbl&gt;    &lt;int&gt;          &lt;int&gt;
##  1  2013     1     1      517            515         2      830            819
##  2  2013     1     1      533            529         4      850            830
##  3  2013     1     1      542            540         2      923            850
##  4  2013     1     1      544            545        -1     1004           1022
##  5  2013     1     1      554            600        -6      812            837
##  6  2013     1     1      554            558        -4      740            728
##  7  2013     1     1      555            600        -5      913            854
##  8  2013     1     1      557            600        -3      709            723
##  9  2013     1     1      557            600        -3      838            846
## 10  2013     1     1      558            600        -2      753            745
## # ... with more rows, and 11 more variables: arr_delay &lt;dbl&gt;, carrier &lt;chr&gt;,
## #   flight &lt;int&gt;, tailnum &lt;chr&gt;, origin &lt;chr&gt;, dest &lt;chr&gt;, air_time &lt;dbl&gt;,
## #   distance &lt;dbl&gt;, hour &lt;dbl&gt;, minute &lt;dbl&gt;, time_hour &lt;dbl&gt;</code></pre>
<p>主要区别:我们看到数据源是来源数据库的远程数据源.</p>
</div>
<div id="查询数据库" class="section level2">
<h2>查询数据库</h2>
<p>我们在数据库中交互式,通常使用 SQL 查询,dbplyr 的目的是自动生成 SQL,但是 SQL 是一种非常大的语言,dbplyr 并不能完全代替,但是作为数据分析常用的 SQL,dbplyr 大部分都能帮助我们自动生成,我们可以继续使用我们熟悉的 dplyr 动词.</p>
<ul>
<li>select</li>
</ul>
<div class="sourceCode" id="cb15"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb15-1"><a href="#cb15-1" aria-hidden="true" tabindex="-1"></a>flights_db <span class="sc">%&gt;%</span> <span class="fu">select</span>(year<span class="sc">:</span>day, dep_delay, arr_delay)</span></code></pre></div>
<pre><code>## # Source:   lazy query [?? x 5]
## # Database: sqlite 3.35.5 [:memory:]
##     year month   day dep_delay arr_delay
##    &lt;int&gt; &lt;int&gt; &lt;int&gt;     &lt;dbl&gt;     &lt;dbl&gt;
##  1  2013     1     1         2        11
##  2  2013     1     1         4        20
##  3  2013     1     1         2        33
##  4  2013     1     1        -1       -18
##  5  2013     1     1        -6       -25
##  6  2013     1     1        -4        12
##  7  2013     1     1        -5        19
##  8  2013     1     1        -3       -14
##  9  2013     1     1        -3        -8
## 10  2013     1     1        -2         8
## # ... with more rows</code></pre>
<div class="sourceCode" id="cb17"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb17-1"><a href="#cb17-1" aria-hidden="true" tabindex="-1"></a>flights_db <span class="sc">%&gt;%</span> <span class="fu">filter</span>(dep_delay <span class="sc">&gt;</span> <span class="dv">240</span>)</span></code></pre></div>
<pre><code>## # Source:   lazy query [?? x 19]
## # Database: sqlite 3.35.5 [:memory:]
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    &lt;int&gt; &lt;int&gt; &lt;int&gt;    &lt;int&gt;          &lt;int&gt;     &lt;dbl&gt;    &lt;int&gt;          &lt;int&gt;
##  1  2013     1     1      848           1835       853     1001           1950
##  2  2013     1     1     1815           1325       290     2120           1542
##  3  2013     1     1     1842           1422       260     1958           1535
##  4  2013     1     1     2115           1700       255     2330           1920
##  5  2013     1     1     2205           1720       285       46           2040
##  6  2013     1     1     2343           1724       379      314           1938
##  7  2013     1     2     1332            904       268     1616           1128
##  8  2013     1     2     1412            838       334     1710           1147
##  9  2013     1     2     1607           1030       337     2003           1355
## 10  2013     1     2     2131           1512       379     2340           1741
## # ... with more rows, and 11 more variables: arr_delay &lt;dbl&gt;, carrier &lt;chr&gt;,
## #   flight &lt;int&gt;, tailnum &lt;chr&gt;, origin &lt;chr&gt;, dest &lt;chr&gt;, air_time &lt;dbl&gt;,
## #   distance &lt;dbl&gt;, hour &lt;dbl&gt;, minute &lt;dbl&gt;, time_hour &lt;dbl&gt;</code></pre>
<div class="sourceCode" id="cb19"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb19-1"><a href="#cb19-1" aria-hidden="true" tabindex="-1"></a>flights_db <span class="sc">%&gt;%</span></span>
<span id="cb19-2"><a href="#cb19-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(dest) <span class="sc">%&gt;%</span></span>
<span id="cb19-3"><a href="#cb19-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">delay =</span> <span class="fu">mean</span>(dep_time))</span></code></pre></div>
<pre><code>## Warning: Missing values are always removed in SQL.
## Use `mean(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.</code></pre>
<pre><code>## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.35.5 [:memory:]
##    dest  delay
##    &lt;chr&gt; &lt;dbl&gt;
##  1 ABQ   2006.
##  2 ACK   1033.
##  3 ALB   1627.
##  4 ANC   1635.
##  5 ATL   1293.
##  6 AUS   1521.
##  7 AVL   1175.
##  8 BDL   1490.
##  9 BGR   1690.
## 10 BHM   1944.
## # ... with more rows</code></pre>
<p>普通的数据框和远程数据库查询之间的最重要区别在于,我们写的 R 代码被翻译成 SQL 并在远程服务器上的数据库中执行,而不是在本地的电脑上的 R 中执行.</p>
<p>在处理数据库时,dbplyr的懒惰特性将:</p>
<ul>
<li>除非我们明确要求,否则数据将永远不在 R 中,也就是永远存在在远程服务器中.</li>
<li>我们可以写很多 R 代码,最后将全部想做的事情,到最后再发给远程服务器的数据库中执行.</li>
</ul>
<p>如下所示:</p>
<div class="sourceCode" id="cb22"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb22-1"><a href="#cb22-1" aria-hidden="true" tabindex="-1"></a>temp <span class="ot">&lt;-</span> flights_db <span class="sc">%&gt;%</span></span>
<span id="cb22-2"><a href="#cb22-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(tailnum) <span class="sc">%&gt;%</span></span>
<span id="cb22-3"><a href="#cb22-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">delay =</span> <span class="fu">mean</span>(arr_delay), <span class="at">n =</span> <span class="fu">n</span>()) <span class="sc">%&gt;%</span></span>
<span id="cb22-4"><a href="#cb22-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">arrange</span>(<span class="fu">desc</span>(delay)) <span class="sc">%&gt;%</span></span>
<span id="cb22-5"><a href="#cb22-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(n <span class="sc">&gt;</span> <span class="dv">100</span>)</span></code></pre></div>
<p>通过运行上述代码,我们发现基本没有运行时间,也就是说上述代码并没有真正和数据库交互运行.直到我们请求数据(打印),dplyr 才会生成 SQL ,并从数据库请求结果.即使是打印数据,也没有将全部的数据参与运行,尽可能少的拉取几行返回.</p>
<p>通过<code>show_query()</code>我们可以看到 dplyr 生成的 SQL:</p>
<div class="sourceCode" id="cb23"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb23-1"><a href="#cb23-1" aria-hidden="true" tabindex="-1"></a>temp <span class="sc">%&gt;%</span> <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## Warning: ORDER BY is ignored in subqueries without LIMIT
## i Do you need to move arrange() later in the pipeline or use window_order() instead?</code></pre>
<pre><code>## &lt;SQL&gt;
## SELECT *
## FROM (SELECT `tailnum`, AVG(`arr_delay`) AS `delay`, COUNT(*) AS `n`
## FROM `flights`
## GROUP BY `tailnum`)
## WHERE (`n` &gt; 100.0)</code></pre>
<p>当我们编写调试完 R 代码后,我们通过<code>collect()</code>将数据从远程数据库中拉取到本地R中.</p>
<div class="sourceCode" id="cb26"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb26-1"><a href="#cb26-1" aria-hidden="true" tabindex="-1"></a>temp_tbl <span class="ot">&lt;-</span> temp <span class="sc">%&gt;%</span> <span class="fu">collect</span>()</span></code></pre></div>
<pre><code>## Warning: ORDER BY is ignored in subqueries without LIMIT
## i Do you need to move arrange() later in the pipeline or use window_order() instead?</code></pre>
<div class="sourceCode" id="cb28"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb28-1"><a href="#cb28-1" aria-hidden="true" tabindex="-1"></a>temp_tbl</span></code></pre></div>
<pre><code>## # A tibble: 1,201 x 3
##    tailnum delay     n
##    &lt;chr&gt;   &lt;dbl&gt; &lt;int&gt;
##  1 &lt;NA&gt;    NA     2512
##  2 N0EGMQ   9.98   371
##  3 N10156  12.7    153
##  4 N10575  20.7    289
##  5 N11106  14.9    129
##  6 N11107  15.0    148
##  7 N11109  14.9    148
##  8 N11113  15.8    138
##  9 N11119  30.3    148
## 10 N11121  10.3    154
## # ... with 1,191 more rows</code></pre>
</div>
<div id="函数解释" class="section level2">
<h2>函数解释</h2>
<div id="内存数据库" class="section level3">
<h3>内存数据库</h3>
<p>由于SQLite 是完全内嵌在 R 中,所以特别合适作为演示数据库,那怎么创建一个临时的数据库呢?如下所示:</p>
<div class="sourceCode" id="cb30"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb30-1"><a href="#cb30-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(RSQLite)</span>
<span id="cb30-2"><a href="#cb30-2" aria-hidden="true" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(<span class="fu">SQLite</span>(), <span class="at">dbname =</span> <span class="st">&quot;:memory:&quot;</span>)</span></code></pre></div>
<p>创建本地数据库 my-db.sqlite,通过这种方法创建的数据库会在当前目录下生成 my-db.sqlite 的文件,该文件即数据库文件.</p>
<div class="sourceCode" id="cb31"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb31-1"><a href="#cb31-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 创建 my-db 并连接</span></span>
<span id="cb31-2"><a href="#cb31-2" aria-hidden="true" tabindex="-1"></a>mydb <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;my-db.sqlite&quot;</span>)</span>
<span id="cb31-3"><a href="#cb31-3" aria-hidden="true" tabindex="-1"></a><span class="co"># 断开连接</span></span>
<span id="cb31-4"><a href="#cb31-4" aria-hidden="true" tabindex="-1"></a><span class="fu">dbDisconnect</span>(mydb)</span>
<span id="cb31-5"><a href="#cb31-5" aria-hidden="true" tabindex="-1"></a><span class="co"># 删除数据库文件</span></span>
<span id="cb31-6"><a href="#cb31-6" aria-hidden="true" tabindex="-1"></a><span class="fu">unlink</span>(<span class="st">&quot;my-db.sqlite&quot;</span>)</span></code></pre></div>
<p>或者是通过<code>dbname = ""</code>建立,该方式会临时在磁盘建立数据库,在关闭连接时会自动删除.</p>
<div class="sourceCode" id="cb32"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb32-1"><a href="#cb32-1" aria-hidden="true" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(<span class="fu">SQLite</span>(), <span class="at">dbname =</span> <span class="st">&quot;&quot;</span>)</span></code></pre></div>
</div>
<div id="tbl" class="section level3">
<h3>tbl</h3>
<p><code>tbl()</code>是 dplyr 里面的动词函数,是一个基于<code>src</code>调度的通用方法,从数据库中返回一个<code>tbl_dbi</code>对象.</p>
<div class="sourceCode" id="cb33"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb33-1"><a href="#cb33-1" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl</span>(src, from, ...)</span></code></pre></div>
<p>src 是一个 DBIConnection 对象,from 参数要么是一个表明,要么是通过 <code>in_schema</code>指定的表名或者<code>sql</code>包裹的 sql 字符串.</p>
<div class="sourceCode" id="cb34"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb34-1"><a href="#cb34-1" aria-hidden="true" tabindex="-1"></a>con <span class="ot">&lt;-</span> DBI<span class="sc">::</span><span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb34-2"><a href="#cb34-2" aria-hidden="true" tabindex="-1"></a><span class="fu">copy_to</span>(con, mtcars)</span>
<span id="cb34-3"><a href="#cb34-3" aria-hidden="true" tabindex="-1"></a>DBI<span class="sc">::</span><span class="fu">dbListTables</span>(con)</span></code></pre></div>
<pre><code>## [1] &quot;mtcars&quot;       &quot;sqlite_stat1&quot; &quot;sqlite_stat4&quot;</code></pre>
<p>通过<code>in_schema</code>查看数据</p>
<div class="sourceCode" id="cb36"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb36-1"><a href="#cb36-1" aria-hidden="true" tabindex="-1"></a>con <span class="sc">%&gt;%</span></span>
<span id="cb36-2"><a href="#cb36-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">tbl</span>(<span class="fu">in_schema</span>(<span class="st">&quot;temp&quot;</span>, <span class="st">&quot;mtcars&quot;</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb36-3"><a href="#cb36-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">head</span>(<span class="dv">1</span>)</span></code></pre></div>
<pre><code>## # Source:   lazy query [?? x 11]
## # Database: sqlite 3.35.5 [:memory:]
##     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
##   &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
## 1    21     6   160   110   3.9  2.62  16.5     0     1     4     4</code></pre>
<p>通过<code>sql</code>查看数据</p>
<div class="sourceCode" id="cb38"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb38-1"><a href="#cb38-1" aria-hidden="true" tabindex="-1"></a>con <span class="sc">%&gt;%</span> <span class="fu">tbl</span>(<span class="fu">sql</span>(<span class="st">&quot;SELECT * FROM mtcars WHERE cyl = 8&quot;</span>))</span></code></pre></div>
<pre><code>## # Source:   SQL [?? x 11]
## # Database: sqlite 3.35.5 [:memory:]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
##    &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
##  1  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
##  2  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
##  3  16.4     8  276.   180  3.07  4.07  17.4     0     0     3     3
##  4  17.3     8  276.   180  3.07  3.73  17.6     0     0     3     3
##  5  15.2     8  276.   180  3.07  3.78  18       0     0     3     3
##  6  10.4     8  472    205  2.93  5.25  18.0     0     0     3     4
##  7  10.4     8  460    215  3     5.42  17.8     0     0     3     4
##  8  14.7     8  440    230  3.23  5.34  17.4     0     0     3     4
##  9  15.5     8  318    150  2.76  3.52  16.9     0     0     3     2
## 10  15.2     8  304    150  3.15  3.44  17.3     0     0     3     2
## # ... with more rows</code></pre>
</div>
<div id="copy_to" class="section level3">
<h3>copy_to</h3>
<p><code>copy_to()</code>是 dplyr 中的函数,该函数功能即将本地数据框上传到远程数据源(数据库).</p>
<div class="sourceCode" id="cb40"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb40-1"><a href="#cb40-1" aria-hidden="true" tabindex="-1"></a><span class="fu">copy_to</span>(dest, df, <span class="at">name =</span> <span class="fu">deparse</span>(<span class="fu">substitute</span>(df)), <span class="at">overwrite =</span> <span class="cn">FALSE</span>, ...)</span></code></pre></div>
</div>
<div id="compute" class="section level3">
<h3>compute</h3>
<ul>
<li><p>compute 将计算后的结果作为临时表直接存在远程数据源中.</p></li>
<li><p>collect 将数据从远程数据源返回本地 R 中.</p></li>
<li><p>collapse 该函数不强制计算,但会强制生成 SQL,方便我们调试.</p></li>
</ul>
<div class="sourceCode" id="cb41"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb41-1"><a href="#cb41-1" aria-hidden="true" tabindex="-1"></a><span class="cf">if</span> (<span class="fu">require</span>(dbplyr)) {</span>
<span id="cb41-2"><a href="#cb41-2" aria-hidden="true" tabindex="-1"></a>  mtcars2 <span class="ot">&lt;-</span> <span class="fu">src_memdb</span>() <span class="sc">%&gt;%</span></span>
<span id="cb41-3"><a href="#cb41-3" aria-hidden="true" tabindex="-1"></a>    <span class="fu">copy_to</span>(mtcars, <span class="at">name =</span> <span class="st">&quot;mtcars2-cc&quot;</span>, <span class="at">overwrite =</span> <span class="cn">TRUE</span>)</span>
<span id="cb41-4"><a href="#cb41-4" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-5"><a href="#cb41-5" aria-hidden="true" tabindex="-1"></a>  remote <span class="ot">&lt;-</span> mtcars2 <span class="sc">%&gt;%</span></span>
<span id="cb41-6"><a href="#cb41-6" aria-hidden="true" tabindex="-1"></a>    <span class="fu">filter</span>(cyl <span class="sc">==</span> <span class="dv">8</span>) <span class="sc">%&gt;%</span></span>
<span id="cb41-7"><a href="#cb41-7" aria-hidden="true" tabindex="-1"></a>    <span class="fu">select</span>(mpg<span class="sc">:</span>drat)</span>
<span id="cb41-8"><a href="#cb41-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-9"><a href="#cb41-9" aria-hidden="true" tabindex="-1"></a>  <span class="co"># 计算查询 将结果保存在远程数据源中</span></span>
<span id="cb41-10"><a href="#cb41-10" aria-hidden="true" tabindex="-1"></a>  <span class="fu">compute</span>(remote)</span>
<span id="cb41-11"><a href="#cb41-11" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-12"><a href="#cb41-12" aria-hidden="true" tabindex="-1"></a>  <span class="co"># 计算查询 将结果返回本次session</span></span>
<span id="cb41-13"><a href="#cb41-13" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collect</span>(remote)</span>
<span id="cb41-14"><a href="#cb41-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-15"><a href="#cb41-15" aria-hidden="true" tabindex="-1"></a>  <span class="co"># 基于生成的 SQL 创建一个查询</span></span>
<span id="cb41-16"><a href="#cb41-16" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collapse</span>(remote)</span>
<span id="cb41-17"><a href="#cb41-17" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<pre><code>## # Source:   lazy query [?? x 5]
## # Database: sqlite 3.35.5 [:memory:]
##      mpg   cyl  disp    hp  drat
##    &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
##  1  18.7     8  360    175  3.15
##  2  14.3     8  360    245  3.21
##  3  16.4     8  276.   180  3.07
##  4  17.3     8  276.   180  3.07
##  5  15.2     8  276.   180  3.07
##  6  10.4     8  472    205  2.93
##  7  10.4     8  460    215  3   
##  8  14.7     8  440    230  3.23
##  9  15.5     8  318    150  2.76
## 10  15.2     8  304    150  3.15
## # ... with more rows</code></pre>
</div>
<div id="memdb_frame-系列" class="section level3">
<h3>memdb_frame 系列</h3>
<p><code>memdb_frame()</code>功能像<code>tibble::tibble()</code>,目的是在临时内存数据库中创建表.</p>
<div class="sourceCode" id="cb43"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb43-1"><a href="#cb43-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 创建临时内存数据库中的表</span></span>
<span id="cb43-2"><a href="#cb43-2" aria-hidden="true" tabindex="-1"></a><span class="fu">memdb_frame</span>(..., <span class="at">.name =</span> <span class="fu">unique_table_name</span>())</span>
<span id="cb43-3"><a href="#cb43-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-4"><a href="#cb43-4" aria-hidden="true" tabindex="-1"></a><span class="co"># 将df copy_to 到临时内存数据库中</span></span>
<span id="cb43-5"><a href="#cb43-5" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl_memdb</span>(df, <span class="at">name =</span> <span class="fu">deparse</span>(<span class="fu">substitute</span>(df)))</span>
<span id="cb43-6"><a href="#cb43-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-7"><a href="#cb43-7" aria-hidden="true" tabindex="-1"></a><span class="co"># 临时内存数据库</span></span>
<span id="cb43-8"><a href="#cb43-8" aria-hidden="true" tabindex="-1"></a><span class="fu">src_memdb</span>()</span></code></pre></div>
<div class="sourceCode" id="cb44"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb44-1"><a href="#cb44-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(dplyr)</span>
<span id="cb44-2"><a href="#cb44-2" aria-hidden="true" tabindex="-1"></a>df <span class="ot">&lt;-</span> <span class="fu">memdb_frame</span>(<span class="at">x =</span> <span class="fu">runif</span>(<span class="dv">100</span>), <span class="at">y =</span> <span class="fu">runif</span>(<span class="dv">100</span>))</span>
<span id="cb44-3"><a href="#cb44-3" aria-hidden="true" tabindex="-1"></a>df <span class="sc">%&gt;%</span> <span class="fu">arrange</span>(x)</span></code></pre></div>
<pre><code>## # Source:     table&lt;dbplyr_002&gt; [?? x 2]
## # Database:   sqlite 3.35.5 [:memory:]
## # Ordered by: x
##           x      y
##       &lt;dbl&gt;  &lt;dbl&gt;
##  1 0.000805 0.386 
##  2 0.0144   0.826 
##  3 0.0195   0.465 
##  4 0.0230   0.0892
##  5 0.0324   0.378 
##  6 0.0490   0.585 
##  7 0.0530   0.650 
##  8 0.0596   0.574 
##  9 0.0655   0.779 
## 10 0.0671   0.572 
## # ... with more rows</code></pre>
<div class="sourceCode" id="cb46"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb46-1"><a href="#cb46-1" aria-hidden="true" tabindex="-1"></a>df <span class="sc">%&gt;%</span></span>
<span id="cb46-2"><a href="#cb46-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">arrange</span>(x) <span class="sc">%&gt;%</span></span>
<span id="cb46-3"><a href="#cb46-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT *
## FROM `dbplyr_002`
## ORDER BY `x`</code></pre>
<div class="sourceCode" id="cb48"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb48-1"><a href="#cb48-1" aria-hidden="true" tabindex="-1"></a>mtcars_db <span class="ot">&lt;-</span> <span class="fu">tbl_memdb</span>(mtcars)</span>
<span id="cb48-2"><a href="#cb48-2" aria-hidden="true" tabindex="-1"></a>mtcars_db <span class="sc">%&gt;%</span></span>
<span id="cb48-3"><a href="#cb48-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(cyl) <span class="sc">%&gt;%</span></span>
<span id="cb48-4"><a href="#cb48-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">n =</span> <span class="fu">n</span>()) <span class="sc">%&gt;%</span></span>
<span id="cb48-5"><a href="#cb48-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT `cyl`, COUNT(*) AS `n`
## FROM `mtcars`
## GROUP BY `cyl`</code></pre>
</div>
<div id="simulate_dbi-系列" class="section level3">
<h3>simulate_dbi 系列</h3>
<p>该系列函数设计用来测试 SQL 语句的生成,而不是真正的连接一个真实的数据库.如下所示:</p>
<p>sqlite</p>
<div class="sourceCode" id="cb50"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb50-1"><a href="#cb50-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(dplyr)</span>
<span id="cb50-2"><a href="#cb50-2" aria-hidden="true" tabindex="-1"></a>df <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">x =</span> <span class="dv">1</span>, <span class="at">y =</span> <span class="dv">2</span>)</span>
<span id="cb50-3"><a href="#cb50-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-4"><a href="#cb50-4" aria-hidden="true" tabindex="-1"></a>df_sqlite <span class="ot">&lt;-</span> <span class="fu">tbl_lazy</span>(df, <span class="at">con =</span> <span class="fu">simulate_sqlite</span>())</span>
<span id="cb50-5"><a href="#cb50-5" aria-hidden="true" tabindex="-1"></a>df_sqlite <span class="sc">%&gt;%</span></span>
<span id="cb50-6"><a href="#cb50-6" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">x =</span> <span class="fu">sd</span>(x, <span class="at">na.rm =</span> <span class="cn">TRUE</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb50-7"><a href="#cb50-7" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT STDEV(`x`) AS `x`
## FROM `df`</code></pre>
<p>mssql</p>
<div class="sourceCode" id="cb52"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb52-1"><a href="#cb52-1" aria-hidden="true" tabindex="-1"></a>df <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">x =</span> <span class="dv">1</span>, <span class="at">y =</span> <span class="dv">2</span>)</span>
<span id="cb52-2"><a href="#cb52-2" aria-hidden="true" tabindex="-1"></a>df_mssql <span class="ot">&lt;-</span> <span class="fu">tbl_lazy</span>(df, <span class="at">con =</span> <span class="fu">simulate_mssql</span>(<span class="at">version =</span> <span class="st">&quot;15.0&quot;</span>))</span>
<span id="cb52-3"><a href="#cb52-3" aria-hidden="true" tabindex="-1"></a>df_mssql <span class="sc">%&gt;%</span></span>
<span id="cb52-4"><a href="#cb52-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">x =</span> <span class="fu">sd</span>(x, <span class="at">na.rm =</span> <span class="cn">TRUE</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb52-5"><a href="#cb52-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT STDEV(`x`) AS `x`
## FROM `df`</code></pre>
<p>simulate_oracle</p>
<div class="sourceCode" id="cb54"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb54-1"><a href="#cb54-1" aria-hidden="true" tabindex="-1"></a>df <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">x =</span> <span class="dv">1</span>, <span class="at">y =</span> <span class="dv">2</span>)</span>
<span id="cb54-2"><a href="#cb54-2" aria-hidden="true" tabindex="-1"></a>df_mssql <span class="ot">&lt;-</span> <span class="fu">tbl_lazy</span>(df, <span class="at">con =</span> <span class="fu">simulate_oracle</span>())</span>
<span id="cb54-3"><a href="#cb54-3" aria-hidden="true" tabindex="-1"></a>df_mssql <span class="sc">%&gt;%</span></span>
<span id="cb54-4"><a href="#cb54-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(<span class="at">z =</span> <span class="fu">paste0</span>(x, y)) <span class="sc">%&gt;%</span></span>
<span id="cb54-5"><a href="#cb54-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT `x` || `y` AS `z`
## FROM (`df`)</code></pre>
<p>以上仅仅列出一小部分,更多函数以及用法请查看 <a href="https://cran.r-project.org/web/packages/dbplyr/dbplyr.pdf">dbplyr的函数手册</a></p>
</div>
<div id="dbplyr-支持的动词" class="section level3">
<h3>dbplyr 支持的动词</h3>
<p>大部分的 dplyr 的动词都支持,如 filter,count,tally,distinct,arrange,inner_join,
left_join,intersect,union,setdiff,mutate,pull,select,rename,slice_min,slice_max
,summarise等.</p>
<p>支持 tidyr 中的动词:pivot_longer,pivot_wider,replace_na,uncount,expand,fill 等</p>
</div>
</div>
<div id="案例" class="section level2">
<h2>案例</h2>
<p>案例以连接 MS SQL Server 和 Oracle 两种数据库为主,我们通过使用 odbc 后端连接 SQL Server 后 使用 dplyr 查询数据库,使用 ROracle 后端连接 oracle 数据库.</p>
<ul>
<li>连接 mssql</li>
</ul>
<div class="sourceCode" id="cb56"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb56-1"><a href="#cb56-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(DBI)</span>
<span id="cb56-2"><a href="#cb56-2" aria-hidden="true" tabindex="-1"></a><span class="co"># mssql</span></span>
<span id="cb56-3"><a href="#cb56-3" aria-hidden="true" tabindex="-1"></a>con_mssql_spb <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(odbc<span class="sc">::</span><span class="fu">odbc</span>(),</span>
<span id="cb56-4"><a href="#cb56-4" aria-hidden="true" tabindex="-1"></a>  <span class="at">.connection_string =</span></span>
<span id="cb56-5"><a href="#cb56-5" aria-hidden="true" tabindex="-1"></a>    <span class="st">&quot;driver={ODBC Driver 17 for SQL Server};</span></span>
<span id="cb56-6"><a href="#cb56-6" aria-hidden="true" tabindex="-1"></a><span class="st">                    server=172.16.88.2;</span></span>
<span id="cb56-7"><a href="#cb56-7" aria-hidden="true" tabindex="-1"></a><span class="st">                    database=spb;</span></span>
<span id="cb56-8"><a href="#cb56-8" aria-hidden="true" tabindex="-1"></a><span class="st">                    uid=zhongyf;</span></span>
<span id="cb56-9"><a href="#cb56-9" aria-hidden="true" tabindex="-1"></a><span class="st">                    pwd=Zyf123456&quot;</span>, <span class="at">timeout =</span> <span class="dv">10</span>, <span class="at">encoding =</span> <span class="st">&quot;gbk&quot;</span></span>
<span id="cb56-10"><a href="#cb56-10" aria-hidden="true" tabindex="-1"></a>)</span></code></pre></div>
<ul>
<li>连接 oracle</li>
</ul>
<div class="sourceCode" id="cb57"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb57-1"><a href="#cb57-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(ROracle)</span>
<span id="cb57-2"><a href="#cb57-2" aria-hidden="true" tabindex="-1"></a>drv <span class="ot">&lt;-</span> <span class="fu">dbDriver</span>(<span class="st">&quot;Oracle&quot;</span>)</span>
<span id="cb57-3"><a href="#cb57-3" aria-hidden="true" tabindex="-1"></a>connect.string <span class="ot">&lt;-</span> <span class="st">&quot;(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)</span></span>
<span id="cb57-4"><a href="#cb57-4" aria-hidden="true" tabindex="-1"></a><span class="st">                    (HOST = 172.16.88.131)(PORT = 1521))</span></span>
<span id="cb57-5"><a href="#cb57-5" aria-hidden="true" tabindex="-1"></a><span class="st">                     (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ghbi) ))&quot;</span></span>
<span id="cb57-6"><a href="#cb57-6" aria-hidden="true" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(drv, <span class="at">username =</span> <span class="st">&quot;pub_query&quot;</span>, <span class="at">password =</span> <span class="st">&quot;pub_query&quot;</span>, <span class="at">dbname =</span> connect.string)</span></code></pre></div>
<p>需要注意ROracle 包的安装,可以参考微信公众号文章<a href="https://mp.weixin.qq.com/s/QLwedZ5mTybqSXdHMTGRIw">R包ROracle安装</a>安装 ROracle.</p>
<div id="ms-sql-server" class="section level3">
<h3>MS SQL Server</h3>
<p>1.2021-01-01至2021-05-01 期间每月销售情况?</p>
<div class="sourceCode" id="cb58"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb58-1"><a href="#cb58-1" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;sales_order&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb58-2"><a href="#cb58-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(<span class="fu">between</span>(单据日期, <span class="st">&quot;2021-01-01&quot;</span>, <span class="st">&quot;2021-05-31&quot;</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb58-3"><a href="#cb58-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(月 <span class="ot">=</span> <span class="fu">month</span>(单据日期)) <span class="sc">%&gt;%</span></span>
<span id="cb58-4"><a href="#cb58-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(</span>
<span id="cb58-5"><a href="#cb58-5" aria-hidden="true" tabindex="-1"></a>    销售额 <span class="ot">=</span> <span class="fu">sum</span>(金额),</span>
<span id="cb58-6"><a href="#cb58-6" aria-hidden="true" tabindex="-1"></a>    销售量 <span class="ot">=</span> <span class="fu">sum</span>(数量)</span>
<span id="cb58-7"><a href="#cb58-7" aria-hidden="true" tabindex="-1"></a>  ) <span class="sc">%&gt;%</span></span>
<span id="cb58-8"><a href="#cb58-8" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collect</span>()</span></code></pre></div>
<pre><code>## Warning: Missing values are always removed in SQL.
## Use `SUM(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.</code></pre>
<pre><code>## # A tibble: 5 x 3
##      月    销售额 销售量
##   &lt;int&gt;     &lt;dbl&gt;  &lt;dbl&gt;
## 1     1 51430112. 198950
## 2     2 68389455. 268733
## 3     3 62714046. 246805
## 4     4 65332434. 259966
## 5     5 76908951. 317079</code></pre>
<p>在上诉案例中需要注意 <code>group_by()</code> 中的<code>month()</code>函数,在 base R 中没有<code>month</code>函数,这涉及到 dbplyr中的一个特性:对于不认识的函数将会保留,那我们就可以使用相应数据库的原生函数.</p>
<p>sql 实现</p>
<div class="sourceCode" id="cb61"><pre class="sourceCode sql"><code class="sourceCode sql"><span id="cb61-1"><a href="#cb61-1" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="dt">month</span>(单据日期) 月,</span>
<span id="cb61-2"><a href="#cb61-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(金额) 销售额,</span>
<span id="cb61-3"><a href="#cb61-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(数量) 销售量</span>
<span id="cb61-4"><a href="#cb61-4" aria-hidden="true" tabindex="-1"></a>  <span class="kw">FROM</span> [spb].[dbo].[sales_order]</span>
<span id="cb61-5"><a href="#cb61-5" aria-hidden="true" tabindex="-1"></a>  <span class="kw">where</span> 单据日期 <span class="kw">between</span> <span class="st">&#39;2021-01-01&#39;</span> <span class="kw">and</span> <span class="st">&#39;2021-05-31&#39;</span></span>
<span id="cb61-6"><a href="#cb61-6" aria-hidden="true" tabindex="-1"></a>  <span class="kw">group</span> <span class="kw">by</span>  <span class="dt">month</span>(单据日期)</span></code></pre></div>
<div class="knitsql-table">
<table>
<caption><span id="tab:unnamed-chunk-28">Table 1: </span>5 records</caption>
<thead>
<tr class="header">
<th align="left">月</th>
<th align="right">销售额</th>
<th align="right">销售量</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1</td>
<td align="right">51430112</td>
<td align="right">198950</td>
</tr>
<tr class="even">
<td align="left">2</td>
<td align="right">68389455</td>
<td align="right">268733</td>
</tr>
<tr class="odd">
<td align="left">3</td>
<td align="right">62714046</td>
<td align="right">246805</td>
</tr>
<tr class="even">
<td align="left">4</td>
<td align="right">65332434</td>
<td align="right">259966</td>
</tr>
<tr class="odd">
<td align="left">5</td>
<td align="right">76908951</td>
<td align="right">317079</td>
</tr>
</tbody>
</table>
</div>
<p>我们看看 dplyr 生成的 SQL 语句</p>
<div class="sourceCode" id="cb62"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb62-1"><a href="#cb62-1" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;sales_order&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb62-2"><a href="#cb62-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(<span class="fu">between</span>(单据日期, <span class="st">&quot;2021-01-01&quot;</span>, <span class="st">&quot;2021-05-31&quot;</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb62-3"><a href="#cb62-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(月 <span class="ot">=</span> <span class="fu">month</span>(单据日期)) <span class="sc">%&gt;%</span></span>
<span id="cb62-4"><a href="#cb62-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(</span>
<span id="cb62-5"><a href="#cb62-5" aria-hidden="true" tabindex="-1"></a>    销售额 <span class="ot">=</span> <span class="fu">sum</span>(金额),</span>
<span id="cb62-6"><a href="#cb62-6" aria-hidden="true" tabindex="-1"></a>    销售量 <span class="ot">=</span> <span class="fu">sum</span>(数量)</span>
<span id="cb62-7"><a href="#cb62-7" aria-hidden="true" tabindex="-1"></a>  ) <span class="sc">%&gt;%</span></span>
<span id="cb62-8"><a href="#cb62-8" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT &quot;月&quot;, SUM(&quot;金额&quot;) AS &quot;销售额&quot;, SUM(&quot;数量&quot;) AS &quot;销售量&quot;
## FROM (SELECT &quot;订单号&quot;, &quot;单据日期&quot;, &quot;SHOP_NO&quot;, &quot;SKU_NO&quot;, &quot;会员编号&quot;, &quot;金额&quot;, &quot;数量&quot;, &quot;吊牌金额&quot;, DATEPART(MONTH, &quot;单据日期&quot;) AS &quot;月&quot;
## FROM &quot;sales_order&quot;
## WHERE (&quot;单据日期&quot; BETWEEN &#39;2021-01-01&#39; AND &#39;2021-05-31&#39;)) &quot;q01&quot;
## GROUP BY &quot;月&quot;</code></pre>
<p>dplyr 生成的 SQL 语句和我们自己编写的语句存在差异,自动生成的 SQL 语句多了一层嵌套,另外<code>month(单据日期)</code> 被翻译为 SQL 中的<code>DATEPART(MONTH, "单据日期") AS "月"</code></p>
<blockquote>
<p>不同数据库对时间日期的处理函数不一致.</p>
</blockquote>
<p>2.2021-01-01至2021-05-01 期间每个大区销售情况?</p>
<div class="sourceCode" id="cb64"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb64-1"><a href="#cb64-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 门店信息表</span></span>
<span id="cb64-2"><a href="#cb64-2" aria-hidden="true" tabindex="-1"></a>store_db <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;store_table&quot;</span>)</span>
<span id="cb64-3"><a href="#cb64-3" aria-hidden="true" tabindex="-1"></a>temp <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;sales_order&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb64-4"><a href="#cb64-4" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(<span class="fu">between</span>(单据日期, <span class="st">&quot;2021-01-01&quot;</span>, <span class="st">&quot;2021-05-31&quot;</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb64-5"><a href="#cb64-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">left_join</span>(store_db, <span class="at">by =</span> <span class="st">&quot;SHOP_NO&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb64-6"><a href="#cb64-6" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(管辖区域) <span class="sc">%&gt;%</span></span>
<span id="cb64-7"><a href="#cb64-7" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(</span>
<span id="cb64-8"><a href="#cb64-8" aria-hidden="true" tabindex="-1"></a>    销售额 <span class="ot">=</span> <span class="fu">sum</span>(金额),</span>
<span id="cb64-9"><a href="#cb64-9" aria-hidden="true" tabindex="-1"></a>    销售量 <span class="ot">=</span> <span class="fu">sum</span>(数量)</span>
<span id="cb64-10"><a href="#cb64-10" aria-hidden="true" tabindex="-1"></a>  ) <span class="sc">%&gt;%</span></span>
<span id="cb64-11"><a href="#cb64-11" aria-hidden="true" tabindex="-1"></a>  <span class="fu">arrange</span>(<span class="fu">desc</span>(销售额))</span></code></pre></div>
<div class="sourceCode" id="cb65"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb65-1"><a href="#cb65-1" aria-hidden="true" tabindex="-1"></a>temp <span class="sc">%&gt;%</span></span>
<span id="cb65-2"><a href="#cb65-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">show_query</span>()</span></code></pre></div>
<pre><code>## &lt;SQL&gt;
## SELECT &quot;管辖区域&quot;, SUM(&quot;金额&quot;) AS &quot;销售额&quot;, SUM(&quot;数量&quot;) AS &quot;销售量&quot;
## FROM (SELECT &quot;订单号&quot;, &quot;单据日期&quot;, &quot;LHS&quot;.&quot;SHOP_NO&quot; AS &quot;SHOP_NO&quot;, &quot;SKU_NO&quot;, &quot;会员编号&quot;, &quot;金额&quot;, &quot;数量&quot;, &quot;吊牌金额&quot;, &quot;门店名称&quot;, &quot;原ERP店编码&quot;, &quot;品牌&quot;, &quot;一级部门&quot;, &quot;门店性质&quot;, &quot;国家&quot;, &quot;管辖区域&quot;, &quot;省份&quot;, &quot;城市&quot;, &quot;城市等级&quot;, &quot;门店负责人&quot;, &quot;区域经理&quot;, &quot;经营状态&quot;, &quot;店铺类型&quot;, &quot;老板&quot;, &quot;加盟商编码&quot;, &quot;加盟商名称&quot;, &quot;重点客户标识&quot;, &quot;年可比店&quot;, &quot;当月可比店&quot;, &quot;门店属性&quot;, &quot;区域&quot;, &quot;仓位类型&quot;, &quot;调整城市&quot;
## FROM (SELECT *
## FROM &quot;sales_order&quot;
## WHERE (&quot;单据日期&quot; BETWEEN &#39;2021-01-01&#39; AND &#39;2021-05-31&#39;)) &quot;LHS&quot;
## LEFT JOIN &quot;store_table&quot; AS &quot;RHS&quot;
## ON (&quot;LHS&quot;.&quot;SHOP_NO&quot; = &quot;RHS&quot;.&quot;SHOP_NO&quot;)
## ) &quot;q01&quot;
## GROUP BY &quot;管辖区域&quot;
## ORDER BY &quot;销售额&quot; DESC</code></pre>
<div class="sourceCode" id="cb67"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb67-1"><a href="#cb67-1" aria-hidden="true" tabindex="-1"></a>temp <span class="sc">%&gt;%</span></span>
<span id="cb67-2"><a href="#cb67-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collect</span>()</span></code></pre></div>
<pre><code>## # A tibble: 5 x 3
##   管辖区域    销售额 销售量
##   &lt;chr&gt;        &lt;dbl&gt;  &lt;dbl&gt;
## 1 华东     95518933. 368410
## 2 华北     79995909. 275645
## 3 华西     75412725. 337205
## 4 华南     73768014. 309546
## 5 总部        79416.    727</code></pre>
<p>SQL 实现</p>
<div class="sourceCode" id="cb69"><pre class="sourceCode sql"><code class="sourceCode sql"><span id="cb69-1"><a href="#cb69-1" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> st.管辖区域,</span>
<span id="cb69-2"><a href="#cb69-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(sa.金额) 销售额,</span>
<span id="cb69-3"><a href="#cb69-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(sa.数量) 销售量</span>
<span id="cb69-4"><a href="#cb69-4" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> [spb].[dbo].[sales_order] sa</span>
<span id="cb69-5"><a href="#cb69-5" aria-hidden="true" tabindex="-1"></a><span class="kw">left</span> <span class="kw">join</span>   [spb].[dbo].[store_table] st <span class="kw">on</span> sa.SHOP_NO <span class="op">=</span> st.SHOP_NO</span>
<span id="cb69-6"><a href="#cb69-6" aria-hidden="true" tabindex="-1"></a><span class="kw">where</span> sa.单据日期 <span class="kw">between</span> <span class="st">&#39;2021-01-01&#39;</span> <span class="kw">and</span> <span class="st">&#39;2021-05-31&#39;</span></span>
<span id="cb69-7"><a href="#cb69-7" aria-hidden="true" tabindex="-1"></a><span class="kw">group</span> <span class="kw">by</span>   st.管辖区域</span>
<span id="cb69-8"><a href="#cb69-8" aria-hidden="true" tabindex="-1"></a><span class="kw">order</span> <span class="kw">by</span>  <span class="fu">sum</span>(sa.金额) <span class="kw">desc</span></span></code></pre></div>
<div class="knitsql-table">
<table>
<caption><span id="tab:unnamed-chunk-32">Table 2: </span>5 records</caption>
<thead>
<tr class="header">
<th align="left">管辖区域</th>
<th align="right">销售额</th>
<th align="right">销售量</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">华东</td>
<td align="right">95518932.72</td>
<td align="right">368410</td>
</tr>
<tr class="even">
<td align="left">华北</td>
<td align="right">79995909.29</td>
<td align="right">275645</td>
</tr>
<tr class="odd">
<td align="left">华西</td>
<td align="right">75412725.15</td>
<td align="right">337205</td>
</tr>
<tr class="even">
<td align="left">华南</td>
<td align="right">73768014.47</td>
<td align="right">309546</td>
</tr>
<tr class="odd">
<td align="left">总部</td>
<td align="right">79416.44</td>
<td align="right">727</td>
</tr>
</tbody>
</table>
</div>
<p>通过对比 dplyr 翻译的 SQL 和原生的 SQL,发现 dplyr 翻译的 SQL 比原生 SQL 会复杂很多,没有优化,运行花费时间会比原生 SQL 长.</p>
<p>2.近30天镜架销售超过100支的SKU,他们的销额,销量,折扣率,动销门店数,动销天数?</p>
<div class="sourceCode" id="cb70"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb70-1"><a href="#cb70-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 门店信息表</span></span>
<span id="cb70-2"><a href="#cb70-2" aria-hidden="true" tabindex="-1"></a>store_db <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;store_table&quot;</span>)</span>
<span id="cb70-3"><a href="#cb70-3" aria-hidden="true" tabindex="-1"></a><span class="co"># 商品信息表</span></span>
<span id="cb70-4"><a href="#cb70-4" aria-hidden="true" tabindex="-1"></a>sku_db <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;sku_table&quot;</span>)</span>
<span id="cb70-5"><a href="#cb70-5" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb70-6"><a href="#cb70-6" aria-hidden="true" tabindex="-1"></a>temp <span class="ot">&lt;-</span> <span class="fu">tbl</span>(con_mssql_spb, <span class="st">&quot;sales_order&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb70-7"><a href="#cb70-7" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(单据日期 <span class="sc">&gt;=</span> <span class="fu">GETDATE</span>() <span class="sc">-</span> 31L) <span class="sc">%&gt;%</span></span>
<span id="cb70-8"><a href="#cb70-8" aria-hidden="true" tabindex="-1"></a>  <span class="co"># GETDATE 是数据库原生函数 思考为什么是减31</span></span>
<span id="cb70-9"><a href="#cb70-9" aria-hidden="true" tabindex="-1"></a>  <span class="fu">left_join</span>(store_db, <span class="at">by =</span> <span class="st">&quot;SHOP_NO&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb70-10"><a href="#cb70-10" aria-hidden="true" tabindex="-1"></a>  <span class="fu">left_join</span>(sku_db, <span class="at">by =</span> <span class="st">&quot;SKU_NO&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb70-11"><a href="#cb70-11" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(分析大类 <span class="sc">==</span> <span class="st">&quot;镜架&quot;</span>) <span class="sc">%&gt;%</span></span>
<span id="cb70-12"><a href="#cb70-12" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(SKU_NO) <span class="sc">%&gt;%</span></span>
<span id="cb70-13"><a href="#cb70-13" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(</span>
<span id="cb70-14"><a href="#cb70-14" aria-hidden="true" tabindex="-1"></a>    销售额 <span class="ot">=</span> <span class="fu">sum</span>(金额),</span>
<span id="cb70-15"><a href="#cb70-15" aria-hidden="true" tabindex="-1"></a>    销售量 <span class="ot">=</span> <span class="fu">sum</span>(数量),</span>
<span id="cb70-16"><a href="#cb70-16" aria-hidden="true" tabindex="-1"></a>    吊牌额 <span class="ot">=</span> <span class="fu">sum</span>(吊牌金额),</span>
<span id="cb70-17"><a href="#cb70-17" aria-hidden="true" tabindex="-1"></a>    动销门店数 <span class="ot">=</span> <span class="fu">n_distinct</span>(<span class="fu">if_else</span>(数量 <span class="sc">&gt;</span> <span class="dv">0</span>, SHOP_NO, <span class="cn">NA_character_</span>)),</span>
<span id="cb70-18"><a href="#cb70-18" aria-hidden="true" tabindex="-1"></a>    动销天数 <span class="ot">=</span> <span class="fu">n_distinct</span>(<span class="fu">if_else</span>(数量 <span class="sc">&gt;</span> <span class="dv">0</span>, 单据日期, <span class="cn">NA_character_</span>))</span>
<span id="cb70-19"><a href="#cb70-19" aria-hidden="true" tabindex="-1"></a>  ) <span class="sc">%&gt;%</span></span>
<span id="cb70-20"><a href="#cb70-20" aria-hidden="true" tabindex="-1"></a>  <span class="fu">mutate</span>(折扣率 <span class="ot">=</span> 销售额 <span class="sc">/</span> 吊牌额) <span class="sc">%&gt;%</span></span>
<span id="cb70-21"><a href="#cb70-21" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(销售量 <span class="sc">&gt;</span> <span class="dv">100</span>) <span class="sc">%&gt;%</span></span>
<span id="cb70-22"><a href="#cb70-22" aria-hidden="true" tabindex="-1"></a>  <span class="fu">arrange</span>(<span class="fu">desc</span>(销售量))</span></code></pre></div>
<p>返回数据集</p>
<div class="sourceCode" id="cb71"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb71-1"><a href="#cb71-1" aria-hidden="true" tabindex="-1"></a>temp_dt <span class="ot">&lt;-</span> temp <span class="sc">%&gt;%</span></span>
<span id="cb71-2"><a href="#cb71-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collect</span>()</span>
<span id="cb71-3"><a href="#cb71-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb71-4"><a href="#cb71-4" aria-hidden="true" tabindex="-1"></a>temp_dt <span class="sc">%&gt;%</span></span>
<span id="cb71-5"><a href="#cb71-5" aria-hidden="true" tabindex="-1"></a>  <span class="fu">slice_head</span>(<span class="at">n =</span> <span class="dv">10</span>)</span></code></pre></div>
<pre><code>## # A tibble: 10 x 7
##    SKU_NO          销售额 销售量 吊牌额 动销门店数 动销天数 折扣率
##    &lt;chr&gt;            &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;      &lt;int&gt;    &lt;int&gt;  &lt;dbl&gt;
##  1 MJ101FF072GYC3 335276.    503 351597        269       30  0.954
##  2 MJ101FG018GDC3 193485.    403 201097        256       30  0.962
##  3 MJ101FF074TYC2 203462.    302 211098        206       30  0.964
##  4 MJ101FF011GDC2 197898.    298 208302        212       30  0.950
##  5 MJ101FG020GYC2 198774.    298 208302        196       30  0.954
##  6 MJ102FG013GUC1 200074.    297 207603        208       30  0.964
##  7 MJ101FF068GRC2 142422.    297 148203        189       30  0.961
##  8 MJ101FG005BKC1 137765.    284 141716        191       30  0.972
##  9 MJ101FG021BKC1 188810.    283 197118        211       30  0.958
## 10 MJ101FG020TEC1 184887.    279 195021        208       30  0.948</code></pre>
<div class="sourceCode" id="cb73"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb73-1"><a href="#cb73-1" aria-hidden="true" tabindex="-1"></a>temp_dt <span class="sc">%&gt;%</span></span>
<span id="cb73-2"><a href="#cb73-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">slice_tail</span>(<span class="at">n =</span> <span class="dv">10</span>)</span></code></pre></div>
<pre><code>## # A tibble: 10 x 7
##    SKU_NO         销售额 销售量 吊牌额 动销门店数 动销天数 折扣率
##    &lt;chr&gt;           &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;      &lt;int&gt;    &lt;int&gt;  &lt;dbl&gt;
##  1 MJ101FG033BKC2 77649.    116  81084        108       29  0.958
##  2 MJ102FG022GYC2 65005.    112  67088         91       30  0.969
##  3 MJ101FG006SLC2 61890.    110  65890         92       28  0.939
##  4 MJ102FF047BKC1 51882.    110  54890         92       29  0.945
##  5 MJ101FF011GDC4 72359.    109  76191         86       29  0.950
##  6 MJ101FF064SLC3 60611.    108  64692         91       28  0.937
##  7 FM1820172C01   68783.    107  74793         89       29  0.920
##  8 MJ101FF011SLC3 69095.    104  71997         84       26  0.960
##  9 FM1820154C03   66632.    104  72696         83       24  0.917
## 10 MJ101FG003BRC3 59080.    102  61098         92       30  0.967</code></pre>
<div class="sourceCode" id="cb75"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb75-1"><a href="#cb75-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 动效门店数最多</span></span>
<span id="cb75-2"><a href="#cb75-2" aria-hidden="true" tabindex="-1"></a>temp_dt <span class="sc">%&gt;%</span></span>
<span id="cb75-3"><a href="#cb75-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">slice_max</span>(动销门店数, <span class="at">n =</span> <span class="dv">5</span>)</span></code></pre></div>
<pre><code>## # A tibble: 6 x 7
##   SKU_NO          销售额 销售量 吊牌额 动销门店数 动销天数 折扣率
##   &lt;chr&gt;            &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;      &lt;int&gt;    &lt;int&gt;  &lt;dbl&gt;
## 1 MJ101FF072GYC3 335276.    503 351597        269       30  0.954
## 2 MJ101FG018GDC3 193485.    403 201097        256       30  0.962
## 3 MJ101FF011GDC2 197898.    298 208302        212       30  0.950
## 4 MJ101FG021BKC1 188810.    283 197118        211       30  0.958
## 5 MJ102FG013GUC1 200074.    297 207603        208       30  0.964
## 6 MJ101FG020TEC1 184887.    279 195021        208       30  0.948</code></pre>
<div class="sourceCode" id="cb77"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb77-1"><a href="#cb77-1" aria-hidden="true" tabindex="-1"></a><span class="co"># 折扣最低</span></span>
<span id="cb77-2"><a href="#cb77-2" aria-hidden="true" tabindex="-1"></a>temp_dt <span class="sc">%&gt;%</span></span>
<span id="cb77-3"><a href="#cb77-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">slice_min</span>(折扣率, <span class="at">n =</span> <span class="dv">5</span>)</span></code></pre></div>
<pre><code>## # A tibble: 5 x 7
##   SKU_NO         销售额 销售量 吊牌额 动销门店数 动销天数 折扣率
##   &lt;chr&gt;           &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;      &lt;int&gt;    &lt;int&gt;  &lt;dbl&gt;
## 1 FM1820154C03   66632.    104  72696         83       24  0.917
## 2 FM1820172C02   94843.    148 103452        119       29  0.917
## 3 FM1820172C01   68783.    107  74793         89       29  0.920
## 4 FM1720084C02   79580.    171  85329        122       28  0.933
## 5 MJ101FF064SLC3 60611.    108  64692         91       28  0.937</code></pre>
</div>
<div id="oracle" class="section level3">
<h3>Oracle</h3>
<p>连接 Oracle 数据库</p>
<div class="sourceCode" id="cb79"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb79-1"><a href="#cb79-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(ROracle)</span>
<span id="cb79-2"><a href="#cb79-2" aria-hidden="true" tabindex="-1"></a>drv <span class="ot">&lt;-</span> <span class="fu">dbDriver</span>(<span class="st">&quot;Oracle&quot;</span>)</span>
<span id="cb79-3"><a href="#cb79-3" aria-hidden="true" tabindex="-1"></a>connect.string <span class="ot">&lt;-</span> <span class="st">&quot;(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)</span></span>
<span id="cb79-4"><a href="#cb79-4" aria-hidden="true" tabindex="-1"></a><span class="st">                    (HOST = 172.16.88.131)(PORT = 1521))</span></span>
<span id="cb79-5"><a href="#cb79-5" aria-hidden="true" tabindex="-1"></a><span class="st">                     (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ghbi) ))&quot;</span></span>
<span id="cb79-6"><a href="#cb79-6" aria-hidden="true" tabindex="-1"></a>con_oracle <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(drv, <span class="at">username =</span> <span class="st">&quot;pub_query&quot;</span>, <span class="at">password =</span> <span class="st">&quot;pub_query&quot;</span>, <span class="at">dbname =</span> connect.string)</span></code></pre></div>
<p>注意 Oracle 中的 Schema 概念，我也不是太明白，但是通过 dbplyr 连接时需要主要，如下所示:</p>
<div class="sourceCode" id="cb80"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb80-1"><a href="#cb80-1" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl</span>(con_oracle, <span class="fu">in_schema</span>(<span class="at">schema =</span> <span class="st">&quot;DW&quot;</span>, <span class="at">table =</span> <span class="st">&quot;DW_SALE_SHOP_F&quot;</span>))</span></code></pre></div>
<pre><code>## # Source:   table&lt;&quot;DW&quot;.&quot;DW_SALE_SHOP_F&quot;&gt; [?? x 33]
## # Database: OraConnection
##    SHOP_NO BILL_DATE           BILL_DATE1          POS_NO    POS_NO1   SKU_NO   
##    &lt;chr&gt;   &lt;dttm&gt;              &lt;dttm&gt;              &lt;chr&gt;     &lt;chr&gt;     &lt;chr&gt;    
##  1 6287    2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0001~ SACA0001~ EM160D00~
##  2 6281    2016-06-12 00:00:00 2016-06-01 00:00:00 SACA0024~ SACA0024~ EM156C00~
##  3 210573  2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0067~ SACA0067~ SA161022~
##  4 210570  2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0040~ SACA0040~ SA161021~
##  5 CW0071  2016-06-01 00:00:00 2016-06-01 00:00:00 SACW0071~ SACW0071~ WPSP00031
##  6 6286    2016-06-05 00:00:00 2016-06-01 00:00:00 SACA0006~ SACA0006~ EM167C00~
##  7 6154    2016-06-01 00:00:00 2016-06-01 00:00:00 SBCW0032~ SBCW0032~ WPSP00108
##  8 210588  2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0143~ SACA0143~ JATRE001~
##  9 210871  2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0147~ SACA0147~ EM160C00~
## 10 6694    2016-06-01 00:00:00 2016-06-01 00:00:00 SACA0091~ SACA0091~ SA161022~
## # ... with more rows, and 27 more variables: CUSTOM_NO &lt;chr&gt;, BILL_QTY &lt;dbl&gt;,
## #   BILL_MONEY &lt;dbl&gt;, BILL_MONEY1 &lt;dbl&gt;, PRICE &lt;dbl&gt;, COST &lt;dbl&gt;,
## #   STOCK_COST &lt;dbl&gt;, SALE_TYPE &lt;chr&gt;, CLUB_NO &lt;chr&gt;, PEOP_NO &lt;chr&gt;,
## #   CURRENCY &lt;chr&gt;, ERP_NO &lt;chr&gt;, CX_NO &lt;chr&gt;, TYPE01 &lt;dbl&gt;, TYPE02 &lt;dbl&gt;,
## #   ETL_DATE &lt;dttm&gt;, POS_QTY &lt;dbl&gt;, GOOS_TYPE &lt;dbl&gt;, DIST_PRICE &lt;dbl&gt;,
## #   BILL_QTY1 &lt;dbl&gt;, SHOP_TYP &lt;dbl&gt;, BILL_MONEY2 &lt;dbl&gt;, POS_ID &lt;dbl&gt;,
## #   QY_CODE &lt;chr&gt;, QD_CODE &lt;chr&gt;, JMS_CODE &lt;chr&gt;, DATA_TYP &lt;dbl&gt;</code></pre>
<p>如果遇到乱码,通过设置语言环境变量即可,详见我的数据处理笔记数据库章节.</p>
<p>在 Oracle 中处理日期问题:2021-01-01至2021-05-01 期间每月销售情况?</p>
<div class="sourceCode" id="cb82"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb82-1"><a href="#cb82-1" aria-hidden="true" tabindex="-1"></a><span class="fu">tbl</span>(con_oracle, <span class="fu">in_schema</span>(<span class="at">schema =</span> <span class="st">&quot;DW&quot;</span>, <span class="at">table =</span> <span class="st">&quot;DW_SALE_SHOP_F&quot;</span>)) <span class="sc">%&gt;%</span></span>
<span id="cb82-2"><a href="#cb82-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">filter</span>(<span class="fu">between</span>(</span>
<span id="cb82-3"><a href="#cb82-3" aria-hidden="true" tabindex="-1"></a>    BILL_DATE1, <span class="fu">to_date</span>(<span class="st">&quot;2021-01-01&quot;</span>, <span class="st">&quot;yyyy-mm-dd&quot;</span>),</span>
<span id="cb82-4"><a href="#cb82-4" aria-hidden="true" tabindex="-1"></a>    <span class="fu">to_date</span>(<span class="st">&quot;2021-05-31&quot;</span>, <span class="st">&quot;yyyy-mm-dd&quot;</span>)</span>
<span id="cb82-5"><a href="#cb82-5" aria-hidden="true" tabindex="-1"></a>  )) <span class="sc">%&gt;%</span></span>
<span id="cb82-6"><a href="#cb82-6" aria-hidden="true" tabindex="-1"></a>  <span class="fu">group_by</span>(月 <span class="ot">=</span> <span class="fu">month</span>(BILL_DATE1)) <span class="sc">%&gt;%</span></span>
<span id="cb82-7"><a href="#cb82-7" aria-hidden="true" tabindex="-1"></a>  <span class="fu">summarise</span>(</span>
<span id="cb82-8"><a href="#cb82-8" aria-hidden="true" tabindex="-1"></a>    销售额 <span class="ot">=</span> <span class="fu">sum</span>(BILL_MONEY2),</span>
<span id="cb82-9"><a href="#cb82-9" aria-hidden="true" tabindex="-1"></a>    销售量 <span class="ot">=</span> <span class="fu">sum</span>(POS_QTY)</span>
<span id="cb82-10"><a href="#cb82-10" aria-hidden="true" tabindex="-1"></a>  ) <span class="sc">%&gt;%</span></span>
<span id="cb82-11"><a href="#cb82-11" aria-hidden="true" tabindex="-1"></a>  <span class="fu">collect</span>()</span></code></pre></div>
<pre><code>## # A tibble: 5 x 3
##      月     销售额 销售量
##   &lt;dbl&gt;      &lt;dbl&gt;  &lt;dbl&gt;
## 1     1 114068765. 402595
## 2     2 137965390. 510844
## 3     4 140264700. 444766
## 4     5 153812950. 514007
## 5     3 131011264. 450359</code></pre>
<p>SQL 实现</p>
<div class="sourceCode" id="cb84"><pre class="sourceCode sql"><code class="sourceCode sql"><span id="cb84-1"><a href="#cb84-1" aria-hidden="true" tabindex="-1"></a><span class="kw">select</span> <span class="fu">EXTRACT</span>(<span class="dt">month</span> <span class="kw">from</span> BILL_DATE1)月 ,</span>
<span id="cb84-2"><a href="#cb84-2" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(BILL_MONEY2) 销售额 ,</span>
<span id="cb84-3"><a href="#cb84-3" aria-hidden="true" tabindex="-1"></a>  <span class="fu">sum</span>(BILL_QTY) 销售量 </span>
<span id="cb84-4"><a href="#cb84-4" aria-hidden="true" tabindex="-1"></a><span class="kw">from</span> DW.DW_SALE_SHOP_F t</span>
<span id="cb84-5"><a href="#cb84-5" aria-hidden="true" tabindex="-1"></a><span class="kw">where</span> BILL_DATE1 <span class="kw">between</span> date<span class="st">&#39;2021-01-01&#39;</span> <span class="kw">and</span> date<span class="st">&#39;2021-05-23&#39;</span> </span>
<span id="cb84-6"><a href="#cb84-6" aria-hidden="true" tabindex="-1"></a><span class="kw">group</span> <span class="kw">by</span>  <span class="fu">EXTRACT</span>(<span class="dt">month</span> <span class="kw">from</span> BILL_DATE1)</span></code></pre></div>
<div class="knitsql-table">
<table>
<caption><span id="tab:unnamed-chunk-38">Table 3: </span>5 records</caption>
<thead>
<tr class="header">
<th align="right">月</th>
<th align="right">销售额</th>
<th align="right">销售量</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="right">1</td>
<td align="right">114068765</td>
<td align="right">498791</td>
</tr>
<tr class="even">
<td align="right">2</td>
<td align="right">137965390</td>
<td align="right">580572</td>
</tr>
<tr class="odd">
<td align="right">4</td>
<td align="right">140264700</td>
<td align="right">634358</td>
</tr>
<tr class="even">
<td align="right">5</td>
<td align="right">123258395</td>
<td align="right">816289</td>
</tr>
<tr class="odd">
<td align="right">3</td>
<td align="right">131011264</td>
<td align="right">585744</td>
</tr>
</tbody>
</table>
</div>
<p>目前我使用的 Oracle 是 12C的版本,该版本目前不支持 month 函数,但是通过 dplyr 生成的 SQL 可以正确运行.不得不说,dplyr 在后台转化为 SQL 时做了很多工作.</p>
</div>
<div id="其他数据库" class="section level3">
<h3>其他数据库</h3>
<p>其他数据库由于使用不多,但是基本常用的数据库都支持良好,大家可以放心使用.</p>
</div>
</div>
<div id="dbplyr-总结" class="section level2">
<h2>dbplyr 总结</h2>
<p>通过上述讲解,我们大概清楚 dbplyr 的作用?将 dplyr 动词转化为 SQL,将生成的 SQL 的发送到远程数据库执行,最后将结果返回.</p>
<p>dbplyr 中最基础的几个函数:</p>
<ul>
<li>tbl / in_schema</li>
<li>show_query /explain</li>
<li>collect</li>
<li>sql</li>
</ul>
<p>由于 dbplyr 的内容比较多,我接触使用也没有太深入,大家可以自行浏览<a href="https://dbplyr.tidyverse.org/articles/dbplyr.html">dbplyr项目地址</a>学习.我们只需掌握最基本的几个函数,能用 dplyr 动词从数据库中获取想要的数据即可.</p>
</div>
<div id="参考资料" class="section level2">
<h2>参考资料</h2>
<p>1.https://cran.r-project.org/web/packages/dbplyr/vignettes/dbplyr.html</p>
<p>2.https://cran.r-project.org/web/packages/dbplyr/vignettes/sql.html</p>
<p>3.https://cran.r-project.org/web/packages/dbplyr/vignettes/translation-verb.html</p>
<p>4.https://cran.r-project.org/web/packages/dbplyr/vignettes/new-backend.html</p>
</div>

    </div>
  </article>

  


</main>

      <footer class="footer">
        <ul class="footer-links">
          <li>
            <a href="/index.xml" type="application/rss+xml" target="_blank">RSS feed</a>
          </li>
          <li>
            <a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备20035539号</a>
          </li>
        </ul>
      </footer>

    </div>
    



<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js"></script>



<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/languages/r.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/languages/yaml.min.js"></script>
<script>hljs.configure({languages: []}); hljs.initHighlightingOnLoad();</script>



    

    
  </body>
</html>

